# app/services/spot_extra_service.py
import pandas as pd
import re
from typing import List, Tuple
from sqlalchemy.orm import Session
from app.models.spot_model import Spot
from io import BytesIO
from app.schemas.spot_extra_schema import SpotExcelRow, SpotOneSentence, SpotCoverBind
class SpotExtraService:

    # 010 Excel 批量导入
    @staticmethod
    def import_excel(db: Session, bytes_data: bytes) -> Tuple[int, str]:
        try:
            df = pd.read_excel(BytesIO(bytes_data))
            df = df.where(pd.notnull(df), None)
            # 关键：把 float 列转字符串
            df["lng"] = df["lng"].astype(str)
            df["lat"] = df["lat"].astype(str)
            for _, row in df.iterrows():
                obj = SpotExcelRow(**row.to_dict())
                if db.query(Spot).filter(Spot.name == obj.name, Spot.city == obj.city).first():
                    continue
                db.add(Spot(**obj.dict()))
            db.commit()
            return 201, "Excel 导入完成"
        except Exception as e:
            db.rollback()
            return 500, f"导入失败: {e}"

    # 011 一句话秒加（极简 NLP + POI 模拟）
    @staticmethod
    def add_by_sentence(db: Session, sentence: str) -> Tuple[int, str, Spot]:
        # 演示级正则抽取
        name = re.search(r"《(.+?)》", sentence) or re.search(r"(.+?)[，,]", sentence)
        city = re.search(r"(\w+市)", sentence)
        desc = sentence
        if not name or not city:
            return 400, "无法解析景点名或城市", None
        name, city = name.group(1), city.group(1)
        if db.query(Spot).filter(Spot.name == name, Spot.city == city).first():
            return 409, "景点已存在", None
        spot = Spot(name=name, city=city, description=desc)
        db.add(spot)
        db.commit()
        db.refresh(spot)
        return 201, "秒加成功", spot

    # 010 官方库批量（模拟 10 条）
    @staticmethod
    def import_official(db: Session) -> Tuple[int, str]:
        official = [
            {"name": "故宫", "city": "北京", "description": "明清两代皇家宫殿，世界文化遗产", "lng": "116.3974",
             "lat": "39.9093"},
            {"name": "西湖", "city": "杭州", "description": "欲把西湖比西子，淡妆浓抹总相宜", "lng": "120.1442",
             "lat": "30.2420"},
            {"name": "黄山", "city": "黄山", "description": "五岳归来不看山，黄山归来不看岳", "lng": "118.1675",
             "lat": "30.1419"},
            {"name": "九寨沟", "city": "阿坝", "description": "人间仙境，童话世界", "lng": "103.9180", "lat": "33.2600"},
            {"name": "张家界", "city": "张家界", "description": "三千奇峰，八百秀水", "lng": "110.4792",
             "lat": "29.3167"},
            {"name": "泰山", "city": "泰安", "description": "五岳之首，登高必自", "lng": "117.1301", "lat": "36.1995"},
            {"name": "华山", "city": "渭南", "description": "奇险天下第一山", "lng": "110.0646", "lat": "34.4830"},
            {"name": "布达拉宫", "city": "拉萨", "description": "世界屋脊的明珠", "lng": "91.1172", "lat": "29.6500"},
            {"name": "丽江古城", "city": "丽江", "description": "世界文化遗产，纳西风情", "lng": "100.2331",
             "lat": "26.8721"},
            {"name": "三亚湾", "city": "三亚", "description": "椰梦长廊，东方夏威夷", "lng": "109.5119",
             "lat": "18.2528"},
        ]
        for item in official:
            exists = db.query(Spot).filter(Spot.name == item["name"], Spot.city == item["city"]).first()
            print("检查重复：", item, "→ 存在" if exists else "→ 不存在")
            if exists:
                continue
            print("准备插入：", item)  # 现在一定会打印（只要不存在）
            db.add(Spot(**item))
        db.commit()
        return 201, "官方库导入完成"

    # 012 批量绑定封面（Excel 导入后统一贴图）
    @staticmethod
    def bind_covers(db: Session, mappings: List[SpotCoverBind]) -> Tuple[int, str]:
        try:
            for m in mappings:
                spot = db.query(Spot).filter(Spot.id == m.spot_id).first()
                if not spot:
                    continue
                file = db.query(File).filter(File.id == m.file_id, File.biz_type == "SPOT_COVER").first()
                if file:
                    spot.cover_url = file.url
            db.commit()
            return 200, "封面绑定成功"
        except Exception as e:
            db.rollback()
            return 500, f"绑定失败: {e}"